/*
 * Class:DBMTest
 * 
 * Description: Test cases of the database transaction
 * 
 * Version 1.0 Sonny Guenette, Marc-Andre Lavoie - 2011-11-19
 * 		Initial implementation
 * 
 * Version 1.1 M-A Lavoie - 2011-12-04
 * 		Updated parameters to create events.
 * 
 * Version 1.2 Sonny Guenette - 2011-12-13
 * 		Finished writing tests
 *      
 * Copyright GPP - Genuine People Personality inc
 * 
 */

package dbManager;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import junit.framework.TestCase;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import calendarEventManager.CEMAgenda;
import calendarEventManager.CEMEvent;

public class DBMTest extends TestCase {
	//global variables
	static DBMService m_oDBService;
	Statement m_oStmt;
	PreparedStatement m_oPStmt;
	ResultSet m_oResult;
	List<CEMAgenda> m_oAgendasList;
	List<CEMEvent> m_oEventsList;
	
	CEMAgenda m_oAgenda;
	CEMEvent m_oEvent;
	
	//agenda variables
	int m_nAgendaId;
	String m_sAgendaName;
	int m_nAgendaColour;
	
	//event variables
	Timestamp m_oEventStart;
	Timestamp m_oEventEnd;
	String m_sEventName;
	int m_nEventDuration;
	String m_sEventDescription;
	
	@Before
	public void setUp() throws Exception {
		//Connect to database
		m_oDBService = DBMService.getInstance();
		
		m_oDBService.TESTinitializeDataBase();
		
		m_oDBService.m_oCon.setAutoCommit(false);
		
		//Initialize agendas list
		m_oAgendasList = new ArrayList<CEMAgenda>();
		m_oEventsList = new ArrayList<CEMEvent>(); 
		
		//Creating the agenda
		m_sAgendaName = "Agenda1";
		m_nAgendaColour = 5;
		
		m_oAgenda = new CEMAgenda(-1, m_sAgendaName, m_nAgendaColour);
		
		//Creating the event
		m_oEventStart = Timestamp.valueOf("2011-01-01 13:00:00");
		m_oEventEnd = Timestamp.valueOf("2011-01-01 14:00:00");
		m_sEventName = "TestEvent1";
		m_sEventDescription = "TestEvent1 Description";
		
		m_oEvent = new CEMEvent(-1, m_sEventName, m_oEventStart, m_oEventEnd, m_sEventDescription, -1);
	}// SetUp()

	@After
	public void tearDown() throws Exception {
		m_oDBService.m_oCon.rollback();
		
		m_oAgenda = null;
		m_oEvent = null;
		m_oAgendasList = null;
		m_oEventsList = null;
		
		//Disconnect from database
		m_oDBService.disconnect();
	}// tearDown()
	
	/*
	 * Test that all the tables we need are created
	 */
	@Test
	public void testCreateTables() throws SQLException{
		//This query return all the tables in our database
		m_oStmt = m_oDBService.m_oCon.createStatement();
		m_oResult = m_oStmt.executeQuery("show tables");
		
		//Moving the cursor to the first line
		m_oResult.next();

		//The first table should be "agendas"
		assertEquals(m_oResult.getString(1), "agendas");
		
		//Moving to the next table
		m_oResult.next();
		
		//The second table should be "events"
		assertEquals(m_oResult.getString(1), "events");

	}// testCreateTables()
	
	/*
	 * Test the methods create, update and delete agenda
	 */
	@Test
	public void testAgendaModification() throws SQLException{
		//Create an agenda
		m_oDBService.createAgenda(m_oAgenda);
		
		m_oStmt = m_oDBService.m_oCon.createStatement();
		m_oResult = m_oStmt.executeQuery("SELECT * FROM agendas");
		
		//Move the cursor to the first line
		m_oResult.next();
		
		//The agendaName should be "Agenda1"
		assertEquals(m_oResult.getString(2), "Agenda1");
		
		//The agendaColour should be 5
		assertEquals(m_oResult.getInt(3), 5);
		
		//Get the agenda id from database and set it to the agenda
		m_oAgenda.setAgendaId(m_oDBService.getAgendaId(m_oAgenda));
		
		//Set new values to the agenda
		m_oAgenda.setAgendaName("ModifAgenda");
		m_oAgenda.setAgendaColour(2);
		
		//Modify agenda1 in the database
		m_oDBService.updateAgenda(m_oAgenda);
		
		m_oResult = m_oStmt.executeQuery("SELECT * FROM agendas");
		
		//Advance to the first line
		m_oResult.next();
		
		assertEquals(m_oResult.getString(2), "ModifAgenda");
		assertEquals(m_oResult.getInt(3), 2);
		
		//delete the agenda
		m_oDBService.deleteAgenda(m_oAgenda.getAgendaId());
		
		m_oResult = m_oStmt.executeQuery("SELECT * FROM agendas");
		
		assertFalse(m_oResult.next());
	}
	
	/*
	 * Test check if the method detect a duplicate 
	 */
	@Test
	public void testDuplicateName() throws SQLException{
		//Creating an agenda
		m_oDBService.createAgenda(m_oAgenda);
		
		//Creating a second agenda, a copy from the first
		String sAgendaName = "Agenda1";
		
		//Test
		assertTrue(m_oDBService.nameAlreadyExist(sAgendaName));
	}
	
	/*
	 * Test if the ID is successfully retrieved and also if the BD modify the ID
	 */
	@Test
	public void testGetAgendaId(){
		//Creating an agenda
		m_oDBService.createAgenda(m_oAgenda);
		
		//Check if the id is retrieved without error
		assertTrue(m_oDBService.getAgendaId(m_oAgenda) != -1);
	}
	
	/*
	 * Test if all the agendas in the database are retrieved
	 */
	@Test
	public void testList(){
		//Creating a first agenda
		m_oDBService.createAgenda(m_oAgenda);
		
		//Creating a second agenda
		m_sAgendaName = "Agenda2";
		m_nAgendaColour = 5;
		
		CEMAgenda oAgenda2 = new CEMAgenda(-1, m_sAgendaName, m_nAgendaColour);
		
		m_oDBService.createAgenda(oAgenda2);
		
		//Make sure the list is empty at first
		assertTrue(m_oAgendasList.isEmpty());
		
		//List them from database. Test SQL errors
		assertTrue(m_oDBService.selectAllTuple(m_oAgendasList));
		
		//Test if the two agendas we added are listed
		assertTrue(m_oAgendasList.size() == 2);
		
	}
	
	@Test
	public void testSelectAllTupleEvent() {
		m_oDBService.createEvent(m_oEvent);
		
		// Test list empty
		assertTrue(m_oEventsList.isEmpty());
		
		// Test no SQL Error
		assertTrue(m_oDBService.selectAllTupleEvent(m_oEventsList, m_oEvent.getAgendaId()));
		
		// Test list contains the event we just create
		assertTrue(m_oEventsList.size() == 1);
	}
	
	@Test
	public void testGetEventId() {
		m_oDBService.createEvent(m_oEvent);
		
		// Test id generated by DB
		assertTrue(m_oDBService.getEventId(m_oEvent) != -1);
	}
	
	@Test
	public void testCheckConflictEvent() {
		m_oDBService.createEvent(m_oEvent);
		
		// Test not conflicting event
		assertTrue(!m_oDBService.checkConflictEvent(new CEMEvent(0, "", Timestamp.valueOf("2011-01-01 14:00:01"), 
				Timestamp.valueOf("2011-01-01 15:00:00"), "", 0)));
		
		// Test start time conflicting
		assertTrue(m_oDBService.checkConflictEvent(new CEMEvent(0, "", Timestamp.valueOf("2011-01-01 13:30:00"), 
				Timestamp.valueOf("2011-01-01 15:00:00"), "", 0)));
		
		// Test end time conflicting
		assertTrue(m_oDBService.checkConflictEvent(new CEMEvent(0, "", Timestamp.valueOf("2011-01-01 12:30:00"), 
				Timestamp.valueOf("2011-01-01 13:30:00"), "", 0)));
		
		// Test start time and end time conflicting
		assertTrue(m_oDBService.checkConflictEvent(new CEMEvent(0, "", Timestamp.valueOf("2011-01-01 12:30:00"), 
				Timestamp.valueOf("2011-01-01 15:30:00"), "", 0)));
	}
	
	/*
	 * Test the methods create, update and delete event
	 */
	
	@Test
	public void testEventModification() throws SQLException{
		//Create an agenda
		m_oDBService.createEvent(m_oEvent);
		
		m_oStmt = m_oDBService.m_oCon.createStatement();
		m_oResult = m_oStmt.executeQuery("SELECT * FROM events");
		
		//Move the cursor to the first line
		m_oResult.next();
		
		// Test event parameters
		assertEquals(m_oResult.getString(2), m_sEventName);
		assertEquals(m_oResult.getTimestamp(3), m_oEventStart);
		assertEquals(m_oResult.getTimestamp(4),m_oEventEnd);
		assertEquals(m_oResult.getString(5), m_sEventDescription);
		
		//Get the event id from database and set it to the event
		m_oEvent.setEventId(m_oDBService.getEventId(m_oEvent));
		
		//Set new values to the event
		m_oEvent.setName("NewEvent");
		m_oEvent.setDescription("new description");
		m_oEvent.setStartingTime(Timestamp.valueOf("2011-01-01 14:00:00"));
		m_oEvent.setEndingTime(Timestamp.valueOf("2011-01-01 15:00:00"));
		
		//Modify event in the database
		m_oDBService.updateEvent(m_oEvent);
		
		m_oResult = m_oStmt.executeQuery("SELECT * FROM events");
		
		//Advance to the first line
		m_oResult.next();
		
		// Test updated event parameters
		assertEquals(m_oResult.getString(2), "NewEvent");
		assertEquals(m_oResult.getTimestamp(3), Timestamp.valueOf("2011-01-01 14:00:00"));
		assertEquals(m_oResult.getTimestamp(4),Timestamp.valueOf("2011-01-01 15:00:00"));
		assertEquals(m_oResult.getString(5), "new description");
		
		//delete the event
		m_oDBService.deleteEvent(m_oEvent.getEventId());
		
		m_oResult = m_oStmt.executeQuery("SELECT * FROM events");
		
		assertFalse(m_oResult.next());
	}
}
